import tkinter as tk
from tkinter import ttk
import mysql.connector
import math

# 获取树种
def fetch_tree_data():
    data = {}
    connection = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        )
        cursor = connection.cursor()
        cursor.execute("SELECT tree_name, id FROM tree_name")
        tree_name_results = cursor.fetchall()
        for tree_name_id, tree_name in tree_name_results:
            binary_fang_data = fetch_binary_fang_data(tree_name_id, None, None)
            unary_equations_data = fetch_unary_equations_data(tree_name_id, None, None)
            ginkgo_data = fetch_ginkgo_data(tree_name_id)
            data[tree_name] = {
                'binary_fang': binary_fang_data,
                'unary_equations': unary_equations_data,
                'ginkgo': ginkgo_data
            }
    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")
    finally:
        if connection and connection.is_connected():
            connection.close()
    return data


# 获取省份
def fetch_province_data():
    connection = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        )
        cursor = connection.cursor()
        cursor.execute("SELECT id, sheng FROM province")
        provinces = cursor.fetchall()
        return {row[1]: row[0] for row in provinces}
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return {}
    finally:
        if connection and connection.is_connected():
            connection.close()

# 获取城市
def fetch_city_data(province_id):
    connection = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        )
        cursor = connection.cursor()
        cursor.execute("SELECT shi FROM city WHERE province_id = %s", (province_id,))
        cities = [row[0] for row in cursor.fetchall()]
        return cities
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return []
    finally:
        if connection and connection.is_connected():
            connection.close()

# 二元
def fetch_binary_fang_data(tree_name_id, province_id, planting_id):
    try:
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        ) as connection:
            with connection.cursor() as cursor:
                cursor.execute("SELECT * FROM binary_fang WHERE tree_name_id = %s AND province_id = %s AND planting_id = %s", (tree_name_id, province_id, planting_id))
                return cursor.fetchone()
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return None
    
def fetch_unary_equations_data(tree_name_id, province_id, planting_id):
    try:
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        ) as connection:
            with connection.cursor() as cursor:
                cursor.execute("SELECT * FROM unary_equations WHERE tree_name_id = %s AND province_id = %s AND planting_id= %s", (tree_name_id, province_id, planting_id))
                return [row[0] for row in cursor.fetchall()]
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return []

            
def fetch_ginkgo_data(tree_name_id, province_id, planting_id):
    try:
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        ) as connection:
            with connection.cursor() as cursor:
                cursor.execute("SELECT * FROM ginkgo WHERE tree_name_id = %s AND province_id = %s AND planting_id= %s", (tree_name_id, province_id, planting_id,))
                return [row[0] for row in cursor.fetchall()]
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return []
    
    
# 获取人工/天然种植
def fetch_planting_data():
    try:
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        ) as connection:
            with connection.cursor() as cursor:
                cursor.execute("SELECT na_ar FROM planting")
                return [row[0] for row in cursor.fetchall()]
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return []

# 保存选择
def save_selection():
    selected_params = {}
    selected_params['tree'] = tree_var.get()
    selected_params['province'] = sheng_var.get()
    selected_params['city'] = shi_var.get()
    selected_params['planting'] = planting_var.get()

    result_var.set(f"已选择:\n树种: {selected_params['tree']}\n省份: {selected_params['province']}\n城市: {selected_params['city']}\n种植方式: {selected_params['planting']}")

# 实例化窗体对象
root = tk.Tk()
root.geometry("1400x600+150+150")
root.title("林地碳汇计算器")
root.attributes("-alpha", 0.9)
root["background"] = "#ffffff"

# 创建树种下拉框

# tree_var.set(tree_options[0] if tree_options else "")
tree_options = fetch_tree_data()
if tree_options:
    default_tree = next(iter(tree_options), None)
    tree_var = tk.StringVar(root)
    tree_var.set(default_tree if default_tree else "")
tk.Label(root, text="选择树种", bg="#ffffff", font=("Arial", 12)).grid(row=0, column=0, padx=10, pady=5, sticky="w")
tree_menu = ttk.Combobox(root, textvariable=tree_var, values=tree_options, state="readonly")
tree_menu.grid(row=1, column=0, padx=10, pady=10, sticky="w")

# 选择省份
sheng_data = fetch_province_data()
sheng_var = tk.StringVar(root)
first_province = next(iter(sheng_data), "")
sheng_var.set(first_province)
tk.Label(root, text="选择省份", bg="#ffffff", font=("Arial", 12)).grid(row=0, column=1, padx=10, pady=5, sticky="w")
sheng_menu = ttk.Combobox(root, textvariable=sheng_var, state="readonly", values=list(sheng_data.keys()))
sheng_menu.grid(row=1, column=1, padx=10, pady=10, sticky="w")

# 选择城市
shi_var = tk.StringVar(root)
shi_var.set("")
tk.Label(root, text="选择城市", bg="#ffffff", font=("Arial", 12)).grid(row=0, column=2, padx=10, pady=5, sticky="w")
shi_menu = ttk.Combobox(root, textvariable=shi_var, state="readonly")
shi_menu.grid(row=1, column=2, padx=10, pady=10, sticky="w")

def update_city_options(event):
    selected_province = sheng_var.get()
    province_id = sheng_data[selected_province]
    city_options = fetch_city_data(province_id)
    shi_menu['values'] = city_options
    shi_var.set(city_options[0] if city_options else "")

sheng_menu.bind("<<ComboboxSelected>>", update_city_options)

# 选择种植方式
planting_var = tk.StringVar(root)
planting_options = fetch_planting_data()
tk.Label(root, text="选择种植方式", bg="#ffffff", font=("Arial", 12)).grid(row=0, column=3, padx=10, pady=5, sticky="w")
planting_menu = ttk.Combobox(root, textvariable=planting_var, values=planting_options, state="readonly")
planting_menu.grid(row=1, column=3, padx=10, pady=10, sticky="w")
planting_var.set(planting_options[0] if planting_options else "")

# 输入框及计算逻辑
def on_entry_click(event):
    if event.widget.get() == event.widget.placeholder:
        event.widget.delete(0, tk.END)
        event.widget.config(fg='black')

def on_focusout(event):
    if event.widget.get() == '':
        event.widget.insert(0, event.widget.placeholder)
        event.widget.config(fg='grey')

def calculate():
    selected_tree = tree_var.get()
    selected_province = sheng_var.get()
    selected_planting = planting_var.get()

    diameter = diameter_entry.get()
    height = height_entry.get()
    quantity = quantity_entry.get()

    try:
        diameter = float(diameter)
        height = float(height)
        quantity = int(quantity)
    except ValueError:
        result_var.set("请输入有效的树径、树高和数量")
        return

    try:
         # 获取省份 ID
        province_id = sheng_data[selected_province]

        # 获取种植方式 ID
        cursor.execute("SELECT id FROM planting WHERE na_ar = %s", (selected_planting,))
        planting_id_result = cursor.fetchone()
        if not planting_id_result:
            result_var.set("未能找到种植方式数据")
            return
        planting_id = planting_id_result[0]

        # 获取树种相关数据
        tree_data = fetch_tree_data()
        if selected_tree in tree_data:
            selected_tree_data = tree_data[selected_tree]
        else:
            result_var.set("未找到所选树种的数据")
            return
        
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='tree'
        ) as connection:
            print("数据库连接成功")
            with connection.cursor() as cursor:
                if selected_tree == "":
                    # 计算二元
                    cursor.execute("SELECT tree_name FROM tree_name WHERE tree_name = %s", (selected_tree,))
                    tree_id = cursor.fetchone()[0]
                    if not tree_id:
                        result_var.set("未能找到树种数据")
                        return
                    tree_id = tree_id[0]

                    province_id = sheng_data[selected_province]

                    cursor.execute("SELECT id FROM planting WHERE na_ar = %s", (selected_planting,))
                    planting_id = cursor.fetchone()[0]
                    if not planting_id:
                        result_var.set("未能找到种植方式数据")
                        return
                    planting_id = planting_id[0]

                    # 计算二元生物量
                    cursor.execute("SELECT c0, c1, c2 FROM binary_fang")
                    result = cursor.fetchone()
                    if result:
                        c0, c1, c2 = result
                    else:
                        result_var.set("未能获取树种数据")
                        return

                    biomass = c0 * (diameter ** c1) * (height ** c2)

                    # 计算二元相容性立木材积模型
                    cursor.execute("SELECT d0, d1, d2 FROM binary_fang")
                    result = cursor.fetchone()
                    if result:
                        d0, d1, d2 = result
                    else:
                        result_var.set("未能获取立木材积数据")
                        return

                    volume = d0 * (diameter ** d1) * (height ** d2)

                    # 计算杨树干材生物量
                    dry_biomass = biomass * volume 

                    # 计算杨树地上分项生物量、生物量和碳库
                    cursor.execute("SELECT dry_skinb0, dry_skinb1, dry_skinb2 FROM binary_fang")
                    result = cursor.fetchone()
                    if result:
                        dry_skinb0, dry_skinb1, dry_skinb2 = result
                    else:
                        result_var.set("未能获取干皮数据")
                        return

                    cursor.execute("SELECT twigb0, twigb1, twigb2 FROM binary_fang")
                    result = cursor.fetchone()
                    if result:
                        twigb0, twigb1, twigb2 = result
                    else:
                        result_var.set("未能获取树枝数据")
                        return

                    cursor.execute("SELECT foliageb0, foliageb1, foliageb2 FROM binary_fang")
                    result = cursor.fetchone()
                    if result:
                        foliageb0, foliageb1, foliageb2 = result
                    else:
                        result_var.set("未能获取树叶数据")
                        return

                    g1 = dry_skinb0 * (diameter ** dry_skinb1) * (height ** dry_skinb2)
                    g2 = twigb0 * (diameter ** twigb1) * (height ** twigb2)
                    g3 = foliageb0 * (diameter ** foliageb1) * (height ** foliageb2)
                    #地上生物量
                    above_ground_biomass = dry_biomass * (1 + g1 + g2 + g3)

                    # 计算地上碳库
                    cursor.execute("SELECT carbon_drywood, carbon_dryskin, carbon_branches FROM binary_fang")
                    result = cursor.fetchone()
                    if result:
                        carbon_drywood, carbon_dryskin, carbon_branches = result
                    else:
                        result_var.set("未能获取树叶数据")
                        return
                    above_ground_carbon_storage = (dry_biomass * (carbon_drywood + g1 * carbon_dryskin + g2 * carbon_branches))
                    
                    #根茎比模型
                    cursor.execute("SELECT e0, e1, e2 FROM binary_fang")
                    result = cursor.fetchone()
                    if result:
                        e0, e1, e2 = result
                    else:
                        result_var.set("未能获取立木材积数据")
                        return
                   
                    rsr = e0 * (diameter ** e1) * (height ** e2)
                    #地下生物量
                    below_ground_biomass = dry_biomass * rsr

                    cursor.execute("SELECT carbon_underground FROM binary_fang")
                    carbon_underground = cursor.fetchone()
                    #地下碳库
                    below_ground_carbon_storage = (biomass * volume * rsr * carbon_underground)
                    #全树生物量
                    total_biomass = above_ground_biomass + below_ground_biomass
                    #重复树生物量
                    total_biomass_repeated = total_biomass * quantity
                    #全树碳库量
                    total_carbon_storage = above_ground_carbon_storage + below_ground_carbon_storage
                    #重复树碳库量
                    total_carbon_storage_repeated = total_carbon_storage * quantity

                    cursor.execute("SELECT annual_growth_rate FROM binary_fang")
                    annual_growth_rate = cursor.fetchone()
                    #计算碳汇
                    carbon_sinks = total_carbon_storage * annual_growth_rate
                    #重复树碳汇
                    total_carbon_sinks_repeated = quantity * carbon_sinks

                    # 显示计算结果
                    result = (f"树种: {selected_tree}\n"
                              f"树径: {diameter} cm\n"
                              f"树高: {height} m\n"
                              f"数量: {quantity}\n"
                              f"干材生物量: {dry_biomass:.4f} t\n"
                              f"全树生物量: {total_biomass:.4f} t\n"
                              f"重复树的生物量: {total_biomass_repeated:.4f} t\n"
                              f"碳库量: {total_carbon_storage:.4f} tC\n"
                              f"重复树碳库量: { total_carbon_storage_repeated:.4f} tC\n"
                              f"碳汇: {carbon_sinks:.4f} tC\n"
                              f"重复树碳汇: {total_carbon_sinks_repeated:.4f} tC")
                    result_var.set(result)
                    
                elif selected_tree == "银杏树":
                    # 获取 ginkgo 表中的参数
                    cursor.execute("SELECT tree_name_id, leaf_biomass_a, leaf_biomass_b, leaf_biomass_c, leaf_biomass_CF, aboveground_biomass_a, aboveground_biomass_b, aboveground_biomass_c, aboveground_biomass_CF, rhizome_biomass_a, rhizome_biomass_b, rhizome_biomass_c, rhizome_biomass_CF, carbon_content_wholetree FROM ginkgo")
                    ginkgo_result = cursor.fetchone()
                    if ginkgo_result:
                        tree_name_id, leaf_biomass_a, leaf_biomass_b, leaf_biomass_c, leaf_biomass_CF, aboveground_biomass_a, aboveground_biomass_b, aboveground_biomass_c, aboveground_biomass_CF, rhizome_biomass_a, rhizome_biomass_b, rhizome_biomass_c, rhizome_biomass_CF = ginkgo_result

                        # 计算银杏生物量
                        ginkgo_biomass =(math.exp(leaf_biomass_a + leaf_biomass_b * math.log(diameter) + leaf_biomass_c * math.log(height)) * leaf_biomass_CF)/1000

                        # 计算银杏全树碳储量
                        
                        aboveground_carbon = math.exp(aboveground_biomass_a + aboveground_biomass_b * math.log(diameter) + aboveground_biomass_c * math.log(height)) * aboveground_biomass_CF
                        leaf_carbon = math.exp(leaf_biomass_a + leaf_biomass_b * math.log(diameter) + leaf_biomass_c * math.log(height)) * leaf_biomass_CF
                        rhizome_carbon = math.exp(rhizome_biomass_a + rhizome_biomass_b * math.log(diameter) + rhizome_biomass_c * math.log(height)) * rhizome_biomass_CF
                        ginkgo_total_carbon = (aboveground_carbon - leaf_carbon + rhizome_carbon)/1000 * carbon_content_wholetree

                        # 计算重复树的总生物量
                        ginkgo_biomass_repeated = ginkgo_biomass * quantity

                        # 计算重复树的全树碳库量
                        ginkgo_carbon_repeated = ginkgo_total_carbon * quantity

                        # 获取年增长率并计算碳汇和总碳汇
                        cursor.execute("SELECT annual_growth_rate FROM binary_fang")
                        annual_growth_rate_result = cursor.fetchone()
                        if annual_growth_rate_result:
                            annual_growth_rate = annual_growth_rate_result[0]
                            carbon_sinks = ginkgo_total_carbon * annual_growth_rate
                            carbon_sinks_repeated = quantity * carbon_sinks

                        # 显示计算结果
                        result = (f"树种: {selected_tree}\n"
                                  f"树径: {diameter} cm\n"
                                  f"树高: {height} m\n"
                                  f"数量: {quantity}\n"
                                  f"银杏生物量: {ginkgo_biomass:.4f} t\n"
                                  f"银杏全树碳储量: {ginkgo_total_carbon:.4f} tC\n"
                                  f"重复树的总生物量: {ginkgo_biomass_repeated:.4f} t\n"
                                  f"重复树的全树碳库量: {ginkgo_carbon_repeated:.4f} tC\n"
                                  f"碳汇: {carbon_sinks:.4f} tC\n"
                                  f"总碳汇: {carbon_sinks_repeated:.4f} tC")
                        result_var.set(result)
                        
                else:
                    # 一元树种
                    if selected_tree in ['其他硬阔', '其他软阔']:
                        cursor.execute("SELECT tree_name FROM tree_name WHERE tree_name = %s", (selected_tree,))
                        tree_id = cursor.fetchone()[0]
                        if not tree_id:
                            result_var.set("未能找到树种数据")
                            return
                        
                        cursor.execute("SELECT above_ground_a, above_ground_b, below_ground_a, below_ground_b, avfolage、avdry_skin、avtwig,carbon_content_wholetree FROM unary_equations WHERE tree_name_id = %s", (tree_id,))
                        result = cursor.fetchone()
                        if result:
                            above_ground_a, above_ground_b, below_ground_a, below_ground_b, carbon_content_wholetree = result
                            
                            # 计算地上生物量
                            above_ground_biomass = (above_ground_a * (diameter ** above_ground_b))/1000
                            
                            # 计算地下生物量
                            below_ground_biomass = (below_ground_a * (diameter ** below_ground_b))/1000

                            # 计算全树总生物量
                            total_biomass = above_ground_biomass + below_ground_biomass

                            # 计算全树碳储量
                            total_tree_carbon_storage = (above_ground_biomass - above_ground_biomass * avfolage / (1 +avdry_skin + avtwig + avfolage) + below_ground_biomass) * carbon_content_wholetree

                            # 计算重复树的总生物量
                            total_biomass_repeated = total_biomass * quantity

                            # 计算重复树的总碳储量
                            total_carbon_storage_repeated = total_tree_carbon_storage * quantity

                            # 获取年增长率并计算碳汇和总碳汇
                            cursor.execute("SELECT annual_growth_rate FROM binary_fang")
                            annual_growth_rate_result = cursor.fetchone()
                            if annual_growth_rate_result:
                                annual_growth_rate = annual_growth_rate_result[0]
                                carbon_sinks = total_tree_carbon_storage * annual_growth_rate
                                total_carbon_sinks_repeated = quantity * carbon_sinks

                            # 显示计算结果
                            result = (f"树种: {selected_tree}\n"
                                      f"树径: {diameter} cm\n"
                                      f"数量: {quantity}\n"
                                      f"地上生物量: {above_ground_biomass:.4f} t\n"
                                      f"地下生物量: {below_ground_biomass:.4f} t\n"
                                      f"全树总生物量: {total_biomass:.4f} t\n"
                                      f"全树碳储量: {total_tree_carbon_storage:.4f} tC\n"
                                      f"重复树的总生物量: {total_biomass_repeated:.4f} t\n"
                                      f"重复树的总碳储量: {total_carbon_storage_repeated:.4f} tC\n"
                                      f"碳汇: {carbon_sinks:.4f} tC\n"
                                      f"总碳汇: {total_carbon_sinks_repeated:.4f} tC")
                            result_var.set(result)
                        else:
                            result_var.set("未能从 unary_equations 表中获取数据")
                    else:
                        result_var.set("未选择杨树、银杏树或特定的其他树种，无法进行特定计算")
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        result_var.set("数据库连接错误")

# 树径输入框
tk.Label(root, text="树径 (cm)", bg="#ffffff", font=("Arial", 12)).grid(row=0, column=4, padx=10, pady=5, sticky="w")
diameter_entry = tk.Entry(root)
diameter_entry.placeholder = "请输入树径(cm)"
diameter_entry.insert(0, diameter_entry.placeholder)
diameter_entry.config(fg='grey')
diameter_entry.bind("<FocusIn>", on_entry_click)
diameter_entry.bind("<FocusOut>", on_focusout)
diameter_entry.grid(row=1, column=4, padx=10, pady=10)

# 树高输入框
tk.Label(root, text="树高 (m)", bg="#ffffff", font=("Arial", 12)).grid(row=0, column=5, padx=10, pady=5, sticky="w")
height_entry = tk.Entry(root)
height_entry.placeholder = "请输入树高(m)"
height_entry.insert(0, height_entry.placeholder)
height_entry.config(fg='grey')
height_entry.bind("<FocusIn>", on_entry_click)
height_entry.bind("<FocusOut>", on_focusout)
height_entry.grid(row=1, column=5, padx=10, pady=10)

# 数量输入框
tk.Label(root, text="数量", bg="#ffffff", font=("Arial", 12)).grid(row=0, column=6, padx=10, pady=5, sticky="w")
quantity_entry = tk.Entry(root)
quantity_entry.placeholder = "请输入树的数量"
quantity_entry.insert(0, quantity_entry.placeholder)
quantity_entry.config(fg='grey')
quantity_entry.bind("<FocusIn>", on_entry_click)
quantity_entry.bind("<FocusOut>", on_focusout)
quantity_entry.grid(row=1, column=6, padx=10, pady=10)

# 计算结果显示
tk.Label(root, text="计算结果显示", bg="#ffffff", font=("Arial", 12)).grid(row=2, column=1, padx=10, pady=5, sticky="w")
result_var = tk.StringVar()
result_label = tk.Label(root, textvariable=result_var, bg="#ffffff", font=("Arial", 12), anchor="w", justify="left")
result_label.grid(row=3, column=0, padx=10, pady=10, sticky="w")

# 计算按钮
calculate_button = tk.Button(root, text="计   算", command=calculate, font=("Arial", 12), width=10, height=1)
calculate_button.grid(row=4, column=8, columnspan=6, pady=20)

# 启动事件循环
root.mainloop()